﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace mojoPortal.Web.RS.SBM.AppCode.Lib
{
    public class RSSBMCommonFunctions : mojoPortal.Web.RS.AppCode.Lib.RSCommonFunctions
    {
        public static List<LocationAddress> GetLocationFullAddress(int companyid)
        {
            List<LocationAddress> locaddress = new List<LocationAddress>();
            String strSQL = "select LocationId,LocationName,Street,City,County,State,Country,Zip,Note,CompanyId from rs_tblLocation where companyid=@companyid order by city, street, state";
            SqlCommand myCommand;
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(strSQL, myConnection);
            SqlDataReader myDataReader;

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            if (myDataReader.HasRows)
            {
                int locationid = 0;
                string street = "";
                string city = "";
                string state = "";
                string zip = "";
                while (myDataReader.Read())
                {
                    locationid = Convert.ToInt32(myDataReader["LocationId"].ToString());
                    street = myDataReader["Street"].ToString();
                    city = myDataReader["City"].ToString();
                    state = myDataReader["State"].ToString();
                    zip = myDataReader["Zip"].ToString();
                    locaddress.Add(new LocationAddress(locationid, street, city, state, zip));
                }
            }
            myConnection.Close();
            return locaddress;
        }
        public static string GetContactFullNameByID(int companyid, int contactid)
        {
            string contactfullname = string.Empty;
            String strSQL = "select FirstName, LastName from rs_tblContact where contactid=@contactid and companyid=@companyid";
            SqlCommand myCommand;
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(strSQL, myConnection);
            SqlDataReader myDataReader;

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@contactid";
            param.Value = contactid;
            myCommand.Parameters.Add(param);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            if (myDataReader.HasRows)
            {
                myDataReader.Read();
                contactfullname = myDataReader["FirstName"].ToString() + " " + myDataReader["LastName"].ToString();
            }
            myConnection.Close();
            return contactfullname;
        }
        public static string GetLocationFullAddressByID(int companyid, int locationid)
        {
            string fulladdress = string.Empty;
            String strSQL = "select LocationId,LocationName,Street,City,County,State,Country,Zip,Note,CompanyId from rs_tblLocation where locationid=@locationid and companyid=@companyid";
            SqlCommand myCommand;
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(strSQL, myConnection);
            SqlDataReader myDataReader;

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@locationid";
            param.Value = locationid;
            myCommand.Parameters.Add(param);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            if (myDataReader.HasRows)
            {
                myDataReader.Read();
                fulladdress = myDataReader["Street"].ToString() + ", ";
                fulladdress = fulladdress + myDataReader["City"].ToString() + ", ";
                fulladdress = fulladdress + myDataReader["State"].ToString() + " " + myDataReader["Zip"].ToString();
            }
            myConnection.Close();
            return fulladdress;
        }
        public static string GetBillingLocationFullAddress(int companyid, int contactid)
        {
            string fulladdress = string.Empty;
            String strSQL = "select l.LocationId,l.LocationName,l.Street,l.City,l.County,l.State,l.Country,l.Zip,l.CompanyId from rs_tblLocation l where l.companyid=@companyid and l.locationid in (select c.billinglocationid from rs_tblcontact c where c.contactid=@contactid and c.companyid=@companyid)";
            SqlCommand myCommand;
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(strSQL, myConnection);
            SqlDataReader myDataReader;

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@contactid";
            param.Value = contactid;
            myCommand.Parameters.Add(param);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            if (myDataReader.HasRows)
            {
                myDataReader.Read();
                fulladdress = myDataReader["Street"].ToString() + ", ";
                fulladdress = fulladdress + myDataReader["City"].ToString() + ", ";
                fulladdress = fulladdress + myDataReader["State"].ToString() + " " + myDataReader["Zip"].ToString();
            }
            myConnection.Close();
            return fulladdress;
        }
        public static int GetLocationIDByInvoiceID(int companyid, int invoiceid)
        {
            int locationid = 0;
            if (companyid > 0 && invoiceid > 0)
            {
                string strSQL = "SELECT LocationId FROM rs_tblInvoice WHERE invoiceid=@invoiceid AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@invoiceid";
                param.Value = invoiceid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    locationid = int.Parse(myDataReader["LocationId"].ToString());
                }
            }
            return locationid;
        }
        public static int GetLocationIDByAppointmentID(int companyid, int appointmentid)
        {
            int locationid = 0;
            if (companyid > 0 && appointmentid > 0)
            {
                string strSQL = "SELECT LocationId FROM rs_tblAppointment WHERE AppointmentID=@AppointmentID AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@AppointmentID";
                param.Value = appointmentid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    locationid = int.Parse(myDataReader["LocationId"].ToString());
                }
            }
            return locationid;
        }
        public static int GetLocationIDByEstimateID(int companyid, int estimateid)
        {
            int locationid = 0;
            if (companyid > 0 && estimateid > 0)
            {
                string strSQL = "SELECT LocationId FROM rs_tblEstimate WHERE estimateid=@estimateid AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@estimateid";
                param.Value = estimateid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    locationid = int.Parse(myDataReader["LocationId"].ToString());
                }
            }
            return locationid;
        }
        
        public static int GetLocationIDByWorkOrderID(int companyid, int WorkOrderid)
        {
            int locationid = 0;
            if (companyid > 0 && WorkOrderid > 0)
            {
                string strSQL = "SELECT LocationId FROM rs_tblWorkOrder WHERE workorderid=@WorkOrderId AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@WorkOrderid";
                param.Value = WorkOrderid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    locationid = int.Parse(myDataReader["LocationId"].ToString());
                }
            }
            return locationid;
        }

        public static int GetWorkOrderIDByInvoiceID(int companyid, int invoiceid)
        {
            int WorkOrderid = 0;
            if (companyid > 0 && invoiceid > 0)
            {
                string strSQL = "SELECT WorkOrderId, LocationId, CompanyId FROM vw_WorkOrderInvoice WHERE InvoiceId=@InvoiceId AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@InvoiceId";
                param.Value = invoiceid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    WorkOrderid = int.Parse(myDataReader["workorderid"].ToString());
                }
            }
            return WorkOrderid;
        }

        public static int GetEstimateIDByEstimateID(int companyid, int estimateid)
        {
            int Estimateid = 0;
            if (companyid > 0 && estimateid > 0)
            {
                string strSQL = "SELECT EstimateId, LocationId, CompanyId FROM vw_Estimate WHERE EstimateId=@EstimateId AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@EstimateId";
                param.Value = estimateid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    Estimateid = int.Parse(myDataReader["EstimateId"].ToString());
                }
            }
            return Estimateid;
        }

        public static int GetEstimateIDByTaskID(int companyid, int taskid)
        {
            int Estimateid = 0;
            if (companyid > 0 && taskid > 0)
            {
                string strSQL = "SELECT EstimateId, LocationId, CompanyId FROM rs_tblEstimateTask WHERE taskid=@taskid AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@taskid";
                param.Value = taskid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    Estimateid = int.Parse(myDataReader["EstimateId"].ToString());
                }
            }
            return Estimateid;
        }
        public static int GetWorkOrderIDByTaskID(int companyid, int taskid)
        {
            int WorkOrderid = 0;
            if (companyid > 0 && taskid > 0)
            {
                string strSQL = "SELECT WorkOrderId, LocationId, CompanyId FROM rs_tblWorkOrderTask WHERE taskid=@taskid AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@taskid";
                param.Value = taskid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    WorkOrderid = int.Parse(myDataReader["workorderid"].ToString());
                }
            }
            return WorkOrderid;
        }
        public static int GetWorkOrderIDByIssueID(int companyid, int issueid)
        {
            int WorkOrderid = 0;
            if (companyid > 0 && issueid > 0)
            {
                string strSQL = "SELECT WorkOrderId, LocationId, CompanyId FROM rs_tblIssue WHERE issueid=@issueid AND CompanyId=@CompanyId";

                SqlCommand myCommand;
                SqlParameter param;
                SqlConnection myConnection;
                myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[GetRSCompanyConnectionString()].ToString());
                myCommand = new SqlCommand(strSQL, myConnection);
                SqlDataReader myDataReader;

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@Companyid";
                param.Value = companyid;
                myCommand.Parameters.Add(param);

                param = new SqlParameter();
                param.SqlDbType = SqlDbType.Int;
                param.ParameterName = "@issueid";
                param.Value = issueid;
                myCommand.Parameters.Add(param);

                myConnection.Open();
                myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
                if (myDataReader.HasRows)
                {
                    myDataReader.Read();
                    WorkOrderid = int.Parse(myDataReader["workorderid"].ToString());
                }
            }
            return WorkOrderid;
        }

    }

}